from data_access.read_db import execute_sql, execute_select
from data_access.db_conn import engine
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, Column, String, Float, Integer

# 读取CSV文件
csv_file_path = '../../data-source/General information of schools.csv'


df = pd.read_csv(csv_file_path)

# 定义数据库表的名称
table_name = 'education'

# 创建表结构（如果尚不存在）
metadata = MetaData()
table = Table(
    table_name, metadata,
    Column('school_name', String(255)),
    Column('url_address', String(255)),
    Column('address', String(255)),
    Column('postal_code', String(255)),
    Column('telephone_no', String(255)),
    Column('fax_no', String(255)),
    Column('email_address', String(255)),
    Column('mrt_desc', String(255)),
    Column('bus_desc', String(255)),
    Column('principal_name', String(255)),
    Column('dgp_code', String(255)),
    Column('zone_code', String(255)),
    Column('type_code', String(255)),
    Column('nature_code', String(255)),
    Column('session_code', String(255)),
    Column('mainlevel_code', String(255)),
    Column('mothertongue1_code', String(255)),
    Column('mothertongue2_code', String(255)),
    Column('mothertongue3_code', String(255)),

)

# 创建表（如果不存在）
metadata.create_all(engine)


# 准备插入数据的SQL语句
insert_query = f"""
INSERT INTO {table_name} (
    school_name, url_address, address, postal_code, telephone_no, fax_no, email_address, 
    mrt_desc, bus_desc, principal_name, dgp_code, zone_code, type_code, nature_code, 
    session_code, mainlevel_code, mothertongue1_code, mothertongue2_code, mothertongue3_code
) VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")
"""

# 插入数据
for index, row in df.iterrows():
    sql = insert_query % (
        row['school_name'], row['url_address'], row['address'], row['postal_code'],
        row['telephone_no'], row['fax_no'], row['email_address'],
        row['mrt_desc'], row['bus_desc'], row['principal_name'], row['dgp_code'],
        row['zone_code'], row['type_code'], row['nature_code'],
        row['session_code'], row['mainlevel_code'], row['mothertongue1_code'],
        row['mothertongue2_code'], row['mothertongue3_code']
    )
    execute_sql(sql)

print("Data has been written to the database.")

